package ServerOperation;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.StandardOpenOption;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;

import java.util.Date;

import java.util.List;

import javax.swing.text.StyledEditorKit.BoldAction;




import Boundry.EvaluationPanel;
import Controllers.GlobalData;
import Controllers.GenericToolsForController.PDFDocument;
import Controllers.GenericToolsForController.PDFfile;
import Controllers.GenericToolsForController.RequestMainStatusEnum;
import Controllers.GenericToolsForController.RequestStepStatusEnum;
import Controllers.GenericToolsForController.WorkerPostion;
import Entity.InfoSystem;
import Entity.Message;
import Entity.Request;
import Entity.RequestStatusHistory;
import Entity.Status;
import Entity.User;
import Entity.Worker;
import Exception.ICMException;

public class DBRequest  implements IDBHandler {
private Connection conn;
	
	public DBRequest(Connection conn) {
		super();
		this.conn = conn;
	}

	@Override
	public Message decipherMessage(Message msg) {
		// TODO Auto-generated method stub
		Message AnswerMessage = null;
		int op = (msg.getOpCode()%1000);
	
		switch (op){
		case 1:
			AnswerMessage =  getAllRequestToCloseRequest(msg);	
			break;
		case 2:
			AnswerMessage =  getAllRequestThatCanBeSuspend(msg);	
			break;
		case 3:
			AnswerMessage =  addNewRequestToDB(msg);	
			break;
		case 4:
			AnswerMessage =  getAllsuspendedRequest(msg);	
			break;
		case 5:
			AnswerMessage =  getAllCloseRequest(msg);	
			break;
		case 6:
			AnswerMessage = getRequestByID(msg);
			break;
		case 7:
			AnswerMessage = getlAllRequestsUnominated(msg);
			break;
		case 8:
			AnswerMessage = UpdateCommitteAndInspector(msg);
			break;
		case 9:
			AnswerMessage = getAllOpenRequest(msg);
			break;
		case 10:
			AnswerMessage = getallintilzedRequest(msg);
			break;
		case 11:
			AnswerMessage = updateRequest_Resume(msg);
			break;
		case 12:
			AnswerMessage = updateRequest_Suspend(msg);
			break;
		case 13:
			AnswerMessage = updateAfterInitTask(msg);
			break;
		case 14:
			System.out.println("Looking for worker number: "+((Worker)msg.getDataGram()).getId());
			AnswerMessage = getAllrequestsByEvaluator(msg);
			break;
		case 15:
			AnswerMessage = closeRequest(msg);
			break;
		case 16:
			AnswerMessage = AssignExaminer(msg);
			break;
		case 17:
			System.out.println("Looking for ChairMan number: "+((Worker)msg.getDataGram()).getId());
			AnswerMessage = getAllRequestsToBeReviewed(msg);
			break;
		case 18:
			AnswerMessage = setEvaluationReport(msg);
            break;
		case 19:
			AnswerMessage = setTestingReport(msg) ;
            break;
		case 20:
			AnswerMessage = ackFotMoreTime(msg);
            break;
		case 21:
			System.out.println("Looking for Examiner number: "+((Worker)msg.getDataGram()).getId());
			AnswerMessage = getAllRequestsToBeTested(msg);
            break;
		case 22:
			AnswerMessage = GenricgetAllRequestByStatusesAndPostion(msg);
			break;
		case 23:
			//AnswerMessage = updateRequestStatusAndHistory(msg);
			break;
		case 24:
			AnswerMessage = getAllRequestbyStatus(msg);
			break;
		case 25:
			AnswerMessage = getAllOpenRequestsByInspector(msg);
			break;
		case 26:
			AnswerMessage = getAllCloseRequestsByInspector(msg);
			break;
		case 27:
			AnswerMessage = getAllSuspendedRequestsByInspector(msg);
			break;
		case 28:
			AnswerMessage = updateStstusToRequest(msg);
			break;
		case 29:
			AnswerMessage = updateVariableInTable(msg);
			break;
			
					}
		
	
		System.out.println("end of cases dbrequest");
		return AnswerMessage;
	}
	
	
	

	
	private Message updateVariableInTable(Message msg) 
	{	 
		boolean result = false;
		if( msg.getDataGram() instanceof String[] )
		{
			String[] arr = (String[]) msg.getDataGram();
			if( arr[0] instanceof String && arr[1] instanceof String && arr[2] instanceof String  )
			{
				String TabelName = "request";
				String rowId = arr[0];
				String varame = arr[1];
				String value = arr[2];
			    result =  GenricOperation.updateVariableInTable( TabelName, rowId, varame, value, conn);
			}
		}
		
		return new Message(msg.getOpCode()+1000, msg.getMessageID(), new Boolean(result));
	}

	private Message getAllSuspendedRequestsByInspector(Message msg) 
	{
		//String[] data = new String[]{inspectorId,mainStatus};
		Message AnswerMessage = null;
				
		if( msg.getDataGram() instanceof String[] )
		{
			String[] data = (String[]) msg.getDataGram();
			String mainStatus = data[1];
			int inspectorId = Integer.parseInt(data[0]);
					
			List<Request> requestlist = GenricOperation.getAllRequestByMainStatusAndInspector(mainStatus,inspectorId,conn);
						
			System.out.println("find  " + requestlist.size() + "Request");
			AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), requestlist);
			return AnswerMessage;
		}
		return  new Message(msg.getOpCode()+1000, msg.getMessageID(), null);
		
	}

	private Message getAllCloseRequestsByInspector(Message msg) 
	{
		//String[] data = new String[]{inspectorId,mainStatus};
		Message AnswerMessage = null;
				
		if( msg.getDataGram() instanceof String[] )
		{
			String[] data = (String[]) msg.getDataGram();
			String mainStatus = data[1];
			int inspectorId = Integer.parseInt(data[0]);
					
			List<Request> requestlist = GenricOperation.getAllRequestByMainStatusAndInspector(mainStatus,inspectorId,conn);
						
			System.out.println("find  " + requestlist.size() + "Request");
			AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), requestlist);
			return AnswerMessage;
		}
		return  new Message(msg.getOpCode()+1000, msg.getMessageID(), null);
	}

	private Message getAllOpenRequestsByInspector(Message msg) 
	{
		//String[] data = new String[]{inspectorId,mainStatus};
		Message AnswerMessage = null;
				
		if( msg.getDataGram() instanceof String[] )
		{
			String[] data = (String[]) msg.getDataGram();
			String mainStatus = data[1];
			int inspectorId = Integer.parseInt(data[0]);
					
			List<Request> requestlist = GenricOperation.getAllRequestByMainStatusAndInspector(mainStatus,inspectorId,conn);
						
			System.out.println("find  " + requestlist.size() + "Request");
			AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), requestlist);
			return AnswerMessage;
		}
		return  new Message(msg.getOpCode()+1000, msg.getMessageID(), null);
	}

	
	private Message getAllRequestbyStatus(Message msg) {
		// TODO Auto-generated method stub
		Message AnswerMessage = null;
		
		 AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), GenricOperation.getAllRequestByStatus((Status) msg.getDataGram(), conn));
		return AnswerMessage;
	}

	
	


	private Message GenricgetAllRequestByStatusesAndPostion(Message msg) {
		Message AnswerMessage = null;
		Status[] stats=null;
		WorkerPostion pos = null;
		int id = 0; 
		System.out.println("Using  GenricgetAllRequestByStatusesAndPostion");
		if (msg.getDataGram() instanceof Object[] &&  (((Object[])msg.getDataGram()).length == 3))
				{
			        Object[] objarr = (Object[]) msg.getDataGram();
			        if (objarr[0]!= null && objarr[0] instanceof Integer && (Integer)objarr[0] >0) 
			        {
						id =(Integer) objarr[0];
				        if (objarr[1]!= null && objarr[1] instanceof WorkerPostion) 
				        {
				    		 pos =(WorkerPostion)objarr[1];
						        if (objarr[2]!= null && objarr[2] instanceof Status[]  && ((Status[])objarr[2]).length >0 ) 
						        {
						        	stats = 	(Status[])objarr[2] ;
						        	//Status stat = new Status(RequestStepStatusEnum.NOMINATED,RequestMainStatusEnum.OPEN);
									List<Request> req =GenricOperation.getAllRequestByStatusAndWorkerPosition(stats,id, pos, conn);
									 //req = GenricOperation.getAllRequestByStatus(stat, conn);
									System.out.println("DB req : "+req.size());
									 AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), req);
									return AnswerMessage;	       	
						        }
				        }
			        }
				}	
		System.out.println("Could not downcast in GenricgetAllRequestByStatusesAndPostion ");
		return new Message(msg.getOpCode()+1000, msg.getMessageID(), null);			
	}

	/***
	 * 
	 * @param msg
	 * @return
	 */
	private Message ackFotMoreTime(Message msg) 
	{
		
		Message AnswerMessage = null;
		
		/*
		 Object[] prams = new Object[3];
		prams[0] = this;
		prams[1] =  new Integer(days);
		prams[2] = reason;
		 */
		if(msg.getDataGram() instanceof Object[])
		{
			 Object[] prams = (Object[]) msg.getDataGram();
			 if(prams[0] instanceof Request)
			 {
				 if(prams[1] instanceof Integer)
				 {
					if(prams[2] instanceof String)
					{
						int days = ((Integer)prams[1]);
						Request req = (Request) prams[0];
						String reason = (String) prams[2];
						RequestStepStatusEnum sStstus=null;
				
						if(req.isInEvluation()) 
						 {
							 sStstus = RequestStepStatusEnum.EVL_EVLUATION_TIME_EXTENSION_PENDING;
						 }
						 else if(req.isInExecuation()) 
						 {
							 sStstus = RequestStepStatusEnum.EXE_UNEXECUTED_TIME_EXTENSION_PENDING;
						 }
						 else if(req.isInReviewAndDecision()) 
						 {
							 sStstus = RequestStepStatusEnum.REV_REVIEWED_TIME_EXTENSION_PENDING;
						 }
						 else if(req.isInTesting()) 
						 {
							 sStstus = RequestStepStatusEnum.TST_UTESTED_TIME_EXTENSION_PENDING;
						 } 
						
						updateStstusToRequest(req.getRequestId(), new Status(sStstus, RequestMainStatusEnum.OPEN), reason, days);
						AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), new Boolean(true));
						return AnswerMessage;
					}
				}
			 }
		}
		return new Message(msg.getOpCode()+1000, msg.getMessageID(), new Boolean(false));
	}

	/**
	 * 
	 * @param msg
	 * @return
	 */
	private Message AssignExaminer(Message msg) 
	{
		
		if(msg.getDataGram() instanceof int[])
		{
			int[] arr =(int[]) msg.getDataGram();
			
			String SpecficQuery = new String("Update request " +
											"set examiner = "+arr[1] +" " +
											"where id ="+arr[0] );
			
			
			System.out.println(SpecficQuery);
			
			Statement stmt;
			
			try {
				stmt = conn.createStatement();
				int ok = stmt.executeUpdate(SpecficQuery);
				System.out.println("OK >? "+ ok );
				Message AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), new Boolean(true));
				return AnswerMessage;
			} 
			catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return  new Message(msg.getOpCode()+1000, msg.getMessageID(), new Boolean(false));
	}

	
	
	
	private Message getAllRequestThatCanBeSuspend(Message msg) 
	{
		//String[] data = new String[]{inspectorId,mainStatus};
		Message AnswerMessage =  new Message(msg.getOpCode()+1000, msg.getMessageID(), null);
		
		if( msg.getDataGram() instanceof String[] )
		{
			String[] data = (String[]) msg.getDataGram();
			String mainStatus = data[1];
			int inspectorId = Integer.parseInt(data[0]);
			
			List<Request> requestlist = GenricOperation.getAllRequestByMainStatusAndInspector(mainStatus,inspectorId,conn);
				
			System.out.println("find  " + requestlist.size() + "Request");
			AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), requestlist);
		}
		return AnswerMessage;
	}
	
	/**
	 * avi you need to fixed this function by the generic :
	 *    public static List<Request> getAllRequestByStatusAndWorkerPosition(Status[] statusArray,int workerId,WorkerPostion position,Connection conn)
	 *  and the return statment
	 * @param msg
	 * @return
	 */
	private Message getAllRequestsToBeReviewed(Message msg)
	{
			
		Message AnswerMessage = null;
		
		
		if( msg.getDataGram() instanceof Worker )
		{
			int chairManId = ((Worker)msg.getDataGram()).getId();
			
			String SpecficQuery = new String("SELECT id " +
					                         "FROM request "+
				                        	 "WHERE `request`.`ChairMan` = ? " );
		System.out.println(SpecficQuery);
		try 
		{
			PreparedStatement stmt = conn.prepareStatement(SpecficQuery);
			stmt.setInt(1, chairManId);
			
			ResultSet rs = stmt.executeQuery();
			
			
			System.out.println("$$$$$$$\n"+rs.toString());
			
			
			List<Request> requestlist = new ArrayList<Request>();
			while (rs.next())
			{
				Request var = GenricOperation.getRequestById(rs.getInt("request.id"), conn);
				requestlist.add(var);
			}
			
			List<Request> temp=null, requestlistreturn;
			requestlistreturn = new ArrayList<Request>();
			temp=requestlist;
			
			
			for (Request req : temp)
			{
				if ((req.isOpen()) && 
					((req.getCurrentStatus().getStatus().getsStatus()==RequestStepStatusEnum.EVL_PASSED)|| 
					(req.getCurrentStatus().getStatus().getsStatus()==RequestStepStatusEnum.REV_REVIEWED_EXTENDED)||
					(req.getCurrentStatus().getStatus().getsStatus()==RequestStepStatusEnum.REV_REVIEWED_TIME_EXTENSION_PENDING)||
					(req.getCurrentStatus().getStatus().getsStatus()==RequestStepStatusEnum.REV_REVIEWED_EXTENDED_DENY)))
				
					requestlistreturn.add(req);
			}
			
			// TODO switch requestlist variable in requestlistreturn in order to get the correct request
			
			AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), requestlist);			
		}
		catch (Exception e)
			{
			// TODO: handle exception
			e.printStackTrace();
			}			
		}
			
		
		/*
			String[] data = (String[]) msg.getDataGram();

			if( msg.getDataGram() instanceof Worker )
				{
			int chairManId =  Integer.parseInt(data[0]);
			
			
			List<Request> temp=null,requestlistreturn;
			requestlistreturn = new ArrayList<Request>();
			
			temp = GenricOperation.getAllRequestByStatus(new Status(RequestStepStatusEnum.EVL_PASSED,RequestMainStatusEnum.OPEN), conn);
			for (Request req : temp)
			{
				if(req.getChairman().getId() == chairManId)
					requestlistreturn.add(req);
			}
			temp = GenricOperation.getAllRequestByStatus(new Status(RequestStepStatusEnum.REV_REVIEWED_EXTENDED,RequestMainStatusEnum.OPEN), conn);
			for (Request req : temp)
			{
				if(req.getChairman().getId() == chairManId)
					requestlistreturn.add(req);
			}
			temp = GenricOperation.getAllRequestByStatus(new Status(RequestStepStatusEnum.REV_REVIEWED_TIME_EXTENSION_PENDING,RequestMainStatusEnum.OPEN), conn);
			for (Request req : temp)
			{
				if(req.getChairman().getId() == chairManId)
					requestlistreturn.add(req);
			}
			temp = GenricOperation.getAllRequestByStatus(new Status(RequestStepStatusEnum.REV_REVIEWED_EXTENDED_DENY,RequestMainStatusEnum.OPEN), conn);
			for (Request req : temp)
			{
				if(req.getChairman().getId() == chairManId)
					requestlistreturn.add(req);
			}
						
			
			if(requestlistreturn.size()>0)
			{
				AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(),requestlistreturn);
			}
			else
			{
				AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), null);
			}
		}	
*/
		System.out.println("return AnswerMessage; =>"+AnswerMessage.toString());
		return AnswerMessage;
	}		
		
	private Message getAllRequestsToBeTested(Message msg)
	{
		
		Message AnswerMessage = null;
		WorkerPostion pos = null;
		int id = 0;
		Object[] objarr = (Object[]) msg.getDataGram();
		if (msg instanceof Message )
		{
			if (objarr[0] instanceof Integer)
			{
				id =(Integer) objarr[0];
			}
			else
				return null;
			if (objarr[1] instanceof  WorkerPostion)
			{
				pos =  (WorkerPostion) objarr[1];
			}
			else return null;
			Status stat = new Status(RequestStepStatusEnum.EVL_PASSED,RequestMainStatusEnum.OPEN);
			Status stat1 = new Status(RequestStepStatusEnum.REV_REVIEWED_EXTENDED,RequestMainStatusEnum.OPEN);
			Status stat2 = new Status(RequestStepStatusEnum.REV_REVIEWED_TIME_EXTENSION_PENDING,RequestMainStatusEnum.OPEN);
			Status stat3 = new Status(RequestStepStatusEnum.REV_REVIEWED_EXTENDED_DENY,RequestMainStatusEnum.OPEN);
			
			List<Request> req =GenricOperation.getAllRequestByStatusAndWorkerPosition(new Status[]{stat,stat1,stat2,stat3},id, pos, conn);
			System.out.println("DB req : "+req.size());
			 AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), req);
			return AnswerMessage;	
		}
		return AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), null);	
		
		
	}		
	
	
	
	
	
	
	private Message getAllrequestsByEvaluator(Message msg) 
	{
		System.out.println("Looking for worker number: "+((Worker)msg.getDataGram()).getId());
		Message AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), null);		
		if( msg.getDataGram() instanceof Worker )
		{
			int evaluatorID = ((Worker)msg.getDataGram()).getId();
			String SpecficQuery = new String("SELECT id " +
					                         "FROM request "+
				                        	 "WHERE `request`.`evlutaor` = ? " );
	System.out.println(SpecficQuery);
	try 
	{
		PreparedStatement stmt = conn.prepareStatement(SpecficQuery);
		stmt.setInt(1, evaluatorID);
		
		ResultSet rs = stmt.executeQuery();
		
		System.out.println(rs.toString());
		List<Request> requestlist = new ArrayList<Request>();
		while (rs.next())
		{
			Request var = GenricOperation.getRequestById(rs.getInt("request.id"), conn);
			requestlist.add(var);
		}	
		AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), requestlist);			
		}
	catch (Exception e) {
		// TODO: handle exception
		e.printStackTrace();
	}			
	}
		return AnswerMessage;

	}

	
	
	

	private Message getAllOpenRequest(Message msg) 
	{
		Message AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), null);
		// Recive dataGram :  String[] data = new String[]{mainStatus};
		if( msg.getDataGram() instanceof String[] )
		{
			String[] data = (String[]) msg.getDataGram();
			String mainStatus = data[0];
			List<Request> requestlist = GenricOperation.getAllRequestByMainStatus(data[0],conn);
			AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), requestlist);
			System.out.println("There is a -" + requestlist.size() + " Of Open Request");
		}
		return AnswerMessage;
	}
	
	
	

	private Message getAllCloseRequest(Message msg) {
		Message AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), null);
		// Recive dataGram :  String[] data = new String[]{mainStatus};
		if( msg.getDataGram() instanceof String[] )
		{
			String[] data = (String[]) msg.getDataGram();
			String mainStatus = data[0];
			List<Request> requestlist = GenricOperation.getAllRequestByMainStatus(data[0],conn);
			AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), requestlist);
			System.out.println("There is a -" + requestlist.size() + " Of Close Request");
		}
		return AnswerMessage;
	}
	
	
	
	
	private Message getAllsuspendedRequest(Message msg)
	{
		Message AnswerMessage = null;
		// Recive dataGram :  String[] data = new String[]{mainStatus};
		if( msg.getDataGram() instanceof String[] )
		{
			String[] data = (String[]) msg.getDataGram();
			String mainStatus = data[0];
			List<Request> requestlist = GenricOperation.getAllRequestByMainStatus(data[0],conn);
			AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), requestlist);
			System.out.println("There is a -" + requestlist.size() + " Of Suspended Request");
			return AnswerMessage;
		}
		return AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), null);
	}

	
	
	private Message updateAfterInitTask(Message msg)
	{
		Message AnswerMessage=null;	
		if( (msg.getDataGram() instanceof HashMap<?,?>)   )
		{
			HashMap<String,Integer> map=  (HashMap<String, Integer>) msg.getDataGram();
				//System.out.println(""+obj[0]+" "+obj[1]);
				/*
				   		map.put("ReqID", reqChosen.getRequestId());
						  map.put("RvrID", reviewer);
						V  map.put("RvrDate", RvrDate);
						V  map.put("EvlId", EvalId);
						 V map.put("ExeId", ExeId);
						 V   map.put("EvlDate", EvalDate);
				 */
		String SpecficQuery = new String("Update request " +
										"set evlutaor = "+map.get("EvlId") +" , executionLeader ="+map.get("ExeId") +" , timeForReview ="+map.get("RvrDate") +" , timeForTesting = "+map.get("EXMDate") +
										" where id ="+map.get("ReqID"));
		
		System.out.println(SpecficQuery);
		
		Statement stmt;
		
		try
		{	
			stmt = conn.createStatement();
			int ok = stmt.executeUpdate(SpecficQuery);
	
			updateStstusToRequest(map.get("ReqID"),new Status(RequestStepStatusEnum.INILIZED,RequestMainStatusEnum.OPEN) ,"Initlized Task",0);
			
			System.out.println("Server - UpdateToDB : Update req ? "+ok);
			
			if (ok == 1 ){
				AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), new Boolean(true));
			}
			else{
				AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), new Boolean(false));
			}
			stmt.close();
			return AnswerMessage;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
	}
	return AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), new Boolean(false));
}
	
	/**
	 * @param msg
	 * @return all requests ready to be initited
	 */
	private Message getallintilzedRequest(Message msg) 
	{
		Message AnswerMessage = null;
		WorkerPostion pos = null;
		int id = 0;
		Object[] objarr = (Object[]) msg.getDataGram();
		if (msg instanceof Message )
		{
			if (objarr[0] instanceof Integer)
			{
				id =(Integer) objarr[0];
			}
			else
				return null;
			if (objarr[1] instanceof  WorkerPostion)
			{
				pos =  (WorkerPostion) objarr[1];
			}
			else return null;
			//public static List<Request> getAllRequestByStatusAndWorkerPosition(Status[] statusArray,int workerId,WorkerPostion position,Connection conn)
			Status stat = new Status(RequestStepStatusEnum.NOMINATED,RequestMainStatusEnum.OPEN);
			List<Request> req =GenricOperation.getAllRequestByStatusAndWorkerPosition(new Status[]{stat},id, pos, conn);
			 //req = GenricOperation.getAllRequestByStatus(stat, conn);
			System.out.println("DB req : "+req.size());
			 AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), req);
			return AnswerMessage;	
		}
		return AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), null);
	}

	
	
	/**
	 * Nominating inspector and commite memeber
	 * 
	 * @param msg
	 * @return
	 */
	private Message UpdateCommitteAndInspector(Message msg)
	{
		if( msg.getDataGram() instanceof int[])
		{
			int[] obj = (int[]) msg.getDataGram();
			System.out.println(""+obj[0]+" "+obj[1]);
			int reqId = obj[0];
			
			Message AnswerMessage;
			
			Statement stmt;
			try 
			{
				stmt = conn.createStatement();
				
				//Assign Stuff : Inspector, Commtee (Update request)
				String SpecficQuery = new String("Update request " +
												"set inspector = "+obj[4] +" , CommitieMember1 ="+obj[2] +" , CommitieMember2 ="+obj[3] +" , ChairMan = "+obj[1]+" " +
												"where id ="+obj[0]);
				
				System.out.println(SpecficQuery);
				int ok = stmt.executeUpdate(SpecficQuery);
				
				updateStstusToRequest(reqId, new Status(RequestStepStatusEnum.NOMINATED, RequestMainStatusEnum.OPEN) ,"NOMINATED Request",0);
			
				System.out.println("Server - UpdateToDB : Add new Request ? "+ok);
				if (ok == 1 ){
					AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), new Boolean(true));
				}
				else{
					AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), new Boolean(false));
				}
				stmt.close();
				return AnswerMessage;
			}
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
		return new Message(msg.getOpCode()+1000, msg.getMessageID(), new Boolean(false));
	}

	
	
	private Message getlAllRequestsUnominated(Message msg) 
	{
		
		Status stat = new Status(RequestStepStatusEnum.INITIAL,RequestMainStatusEnum.OPEN);
		List<Request> req = GenricOperation.getAllRequestByStatus(stat, conn);
		Message AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), req); 
		return AnswerMessage;
		
		/*
		String SpecficQuery = new String ("select * " +
										  "from request r,  history h " +
										  "where  h.id = r.currentStep and h.Status = 1");
		System.out.println(SpecficQuery);
		Message AnswerMessage = null;
		Statement stmt;
		
			try {
				stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(SpecficQuery); 
				ArrayList<Request> reqs = new ArrayList<Request>();
				while(rs.next()){
					reqs.add(new Request(rs.getInt("id"),GenricOperation.getInfoSystem(rs.getInt("infoSystem"), conn),rs.getDate("dayOfCreation") , rs.getString("Description") ) );
					
				}
				AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), reqs); 
				return AnswerMessage;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			*/
	}

	/**
	 * 
	 * @param msg
	 * @return Message with request fully from db
	 */
	private Message getRequestByID(Message msg) 
	{
		if(msg.getDataGram() instanceof Integer )
		{
			Request req = GenricOperation.getRequestById((Integer)msg.getDataGram(), conn);
			return new Message(msg.getOpCode()+1000, msg.getMessageID(), req);
		}
		return new Message(msg.getOpCode()+1000, msg.getMessageID(), null);
	}

	
	private Message addNewRequestToDB(Message msg) 
	{
		Message AnswerMessage = null;
		
		Request req = (Request) msg.getDataGram();
		
		String description = req.getDescription();
		String currentState = req.getCurrentState();
		String execuse = req.getExecuse();
		InfoSystem infoSystem = req.getInfoSystem();
		String notes = req.getNotes();
		PDFfile file = req.getFile();
		
		int reqId = GenricOperation.getNextId("request",conn);
		
		String SpecficQuery = new String("INSERT INTO request (Description, dayOfCreation , currentState , excuse, infoSystem ,Note , initiator) " +
				                         "VALUES ('" + description + "',NOW(),'" + currentState + "', '" + execuse + "', " 
				                         			+ infoSystem.getInfoSystemId() +", '"+ notes + "' ," + req.getInitiator().getUserId() + ")");
		System.out.println(SpecficQuery);
		try 
		{
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(SpecficQuery);
			System.out.println("Server - addNewRequestToDB : Add new Request ");
			
			// if there is a file to add
			if(file!=null)
			{ 
	            String filePath = GenricOperation.createFileInServer(req.getInitiator(), reqId +"", file);
	            
	            // Add file Path name to req.
	            String UpdateFilePath = new String("UPDATE request " +
	            								   "SET File='" + filePath + "' " +
	            								   "WHERE id=" + reqId );
	            stmt.executeUpdate(UpdateFilePath);  
	            
	            System.out.println("Server - Add file Path name to req. ");
			}
			
			updateStstusToRequest(reqId, new Status(RequestStepStatusEnum.INITIAL, RequestMainStatusEnum.OPEN) ,"Adding new Request",0);
		
			AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), new Integer(reqId));
		}
		catch (SQLException e) 
		{
			AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), false);
			e.printStackTrace();
		}
		return AnswerMessage;
	}

	
	
	private Message getAllRequestToCloseRequest(Message msg)
	{
		Message AnswerMessage = null;
		
		// Recive dataGram :  String[] data = new String[]{inspectorId,mainStatus,stepStatus1,stepStatus2};
		
		if( msg.getDataGram() instanceof String[] )
		{
			String[] data = (String[]) msg.getDataGram();
			int inspectorId =  Integer.parseInt(data[0]);
			String mainStatus = data[1];
			String stepStatus1 = data[2];
			String stepStatus2 = data[3];
			
			Status stat1 = new Status(stepStatus1,mainStatus);
			Status stat2 = new Status(stepStatus2,mainStatus);
			Status[] statusArray = new Status[]{stat1,stat2};
			
			List<Request> requestlistreturn = GenricOperation.getAllRequestByStatusAndWorkerPosition(statusArray,inspectorId,WorkerPostion.INSPECTOR,conn);
			
			return new Message(msg.getOpCode()+1000, msg.getMessageID(),requestlistreturn);
		}	
		return new Message(msg.getOpCode()+1000, msg.getMessageID(),null);
	}

	
	
	private Message updateRequest_Resume(Message msg) 
	{
		Message AnswerMessage = null;	
		try {
			if( msg.getDataGram() instanceof Request )
			{
				Request req = (Request) msg.getDataGram();
				int days=0;
				RequestStatusHistory prevHistory = GenricOperation.getHistoryById(req.getCurrentStatus().getPrevHistory(), conn);
				if(prevHistory.getDueDate()!=null)
				{
					System.out.println(prevHistory.getDueDate().getTime());
					System.out.println(prevHistory.getStartDate().getTime());
					float fdays = (prevHistory.getDueDate().getTime() - prevHistory.getStartDate().getTime())/(24 * 60 * 60 * 1000);
					days = (int)Math.ceil(fdays);
				}
				updateStstusToRequest(req.getRequestId(),
						new Status(req.getCurrentStatus().getStatus().getsStatus(),RequestMainStatusEnum.OPEN),
						"Resume The Request",days);
				AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), new Boolean(true));
			}
			else
			{
				AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), new Boolean(false));	
			}
			
			return AnswerMessage;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return new Message(msg.getOpCode()+1000, msg.getMessageID(), new Boolean(false));
		}
	}
	
	
	
	
	private Message updateRequest_Suspend(Message msg)
	{
        System.out.println("private Message updateRequest_Suspend(Message msg)");
		Message AnswerMessage = null;
		if( msg.getDataGram() instanceof Request )
		{
			Request req = (Request) msg.getDataGram();
			
			updateStstusToRequest(req.getRequestId(),
					new Status(req.getCurrentStatus().getStatus().getsStatus(), RequestMainStatusEnum.SUSPEND),
					"Suspend the suspend from the " + req.getCurrentStatus().getStatus().getsStatus() +"Step",
					0);
			AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), new Boolean(true));	
		}
		else
		{
			AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), new Boolean(false));	
		}
		
		return AnswerMessage;	
	}
	
	
	private Message closeRequest(Message msg) 
	{
	       System.out.println("private Message closeRequest(Message msg) ");
			Message AnswerMessage = null;
			if( msg.getDataGram() instanceof Integer )
			{
				int reqId = (Integer) msg.getDataGram();

				updateStstusToRequest(reqId,
						new Status(RequestStepStatusEnum.CLS_PASSED, RequestMainStatusEnum.CLOSE),
						"Close The Request",
						0);
				AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), new Boolean(true));	
			}
			else
			{
				AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), new Boolean(false));	
			}
			
			return AnswerMessage;
	}
	

public Message updateStstusToRequest(Message msg) 
{
	try
	{
	if (msg.getDataGram() != null &&  (msg.getDataGram() instanceof Object[]) && 
			                          ((Object[])msg.getDataGram())[0] instanceof Integer &&
                                      ((Object[])msg.getDataGram())[1] instanceof Status &&
                                      ((Object[])msg.getDataGram())[2] instanceof String &&
                                      ((Object[])msg.getDataGram())[3] instanceof Integer )
                                       {
		                             Integer requestId = (Integer)(((Object[])msg.getDataGram())[0]);
		                             Status newStatus =(Status)(((Object[])msg.getDataGram())[1]) ;
		                             String commentToHistory = (String)(((Object[])msg.getDataGram())[2]);
		                             int dayOfStep = (Integer)(((Object[])msg.getDataGram())[3]);
		                             updateStstusToRequest(requestId, newStatus,commentToHistory,dayOfStep);
                                       }
							else
							{
								throw new ICMException("Could not downcasr in  updateStstusToRequest(Message msg)");
							}
		
	}
	catch (ICMException e) 
	{
		e.printStackTrace();
		
		return new Message(msg.getOpCode()+1000, msg.getMessageID(), new Boolean(false));
	}
		return new Message(msg.getOpCode()+1000, msg.getMessageID(), new Boolean(true));
}

								
                                      
                                              	
	

private void updateStstusToRequest(int requestId, Status newStatus,String commentToHistory,int dayOfStep)
	{
		Request req = GenricOperation.getRequestById(requestId, conn);
		RequestStatusHistory his = req.getCurrentStatus();
		String prevHistory;
		if(his != null)
				prevHistory = his.getHistoryId()+"";
		else 
			prevHistory=null;
		 
		 Statement stmt;
		try 
		{
			stmt = conn.createStatement();
			
			int historyId = GenricOperation.getNextId("history",conn);
			
			 //insert a new row to History 
			 String historyRS;
			 String dueDate = null;
			 if(dayOfStep>0)
			{
				 Date due=new Date();
				 due.setTime(due.getTime() + dayOfStep * 24 * 60 * 60 * 1000);
				 dueDate = new SimpleDateFormat("yyyy-MM-dd").format(due);
				 dueDate = "'"+dueDate+"'";
			}	
			 
			
			if(prevHistory != null)
				 historyRS = new String("INSERT INTO history (StartDate ,DueDate , Comment , Status,prevHistory) "+  
			 				"VALUES (NOW(), " + dueDate + ",'" +commentToHistory + "',"+ GenricOperation.getIdStatus(newStatus,conn) + " , " + prevHistory + " )");
			else 
				 historyRS = new String("INSERT INTO history (StartDate ,DueDate , Comment , Status) "+  
			 				"VALUES (NOW(), " + dueDate + ",'" +commentToHistory + "',"+ GenricOperation.getIdStatus(newStatus,conn)+ ")");
			
						
			System.out.println("updateStstusToRequest uses query: "+historyRS);
			stmt.executeUpdate(historyRS);
			System.out.println("Server - add history row ");
			
			
			//Add RequestHistory int historyId = getNextId("history");
			String RequestHistoryRS = new String("INSERT INTO requesthistory (`reqID`,`historyID`)"+
													 "VALUES(" + req.getRequestId() + "," + historyId + ")" );
				
			stmt.executeUpdate(RequestHistoryRS);
			
			//Add CurrentStstus To Req
			 String UpdateCurrentStstush = new String("UPDATE request " +
					   								   "SET currentStep = '" + historyId + "' " +
					   								   "WHERE id = " + req.getRequestId() );
			 stmt.executeUpdate(UpdateCurrentStstush);  
				
			 if(prevHistory != null)
				{
					// Finish the previous step, set the "EndDate" to be now.
					String UpdateEndTime = new String("UPDATE history " +
							  						  "SET EndDate = NOW(), nextHistory = " + historyId + " " + 
							                          "WHERE id = " + prevHistory );
					 
					stmt.executeUpdate(UpdateEndTime);  
				}
			 stmt.close();
		} 
		catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			
			e.printStackTrace();
		}
		
		
	
		
	}
	 
	public Message setEvaluationReport(Message msg) 
	{
		
		int reportID = -1;
		Request req = null;
		System.out.println("setEvaluationReport before reflexion");
	
		if (msg.getDataGram() instanceof Object[] && ((Object[])msg.getDataGram())[0] != null &&
			                                       	((Object[])msg.getDataGram())[1] != null &&
						                            ((Object[])msg.getDataGram())[0] instanceof Integer &&
				                                     ((Object[])msg.getDataGram())[1] instanceof Request &&
				                                     (Integer)((Object[])msg.getDataGram())[0] > 0)
		{
			 reportID = (Integer)((Object[])msg.getDataGram())[0];
			  req =  (Request) ((Object[])msg.getDataGram())[1] ;
			System.out.println("setEvaluationReport after reflexion");
			Message AnswerMessage;
			
			Statement stmt;
			try 
			{
				stmt = conn.createStatement();
				System.out.println("after create stsmt");
		
				String SpecficQuery=null;

				//Assign Stuff : Inspector, Commtee (Update request)
				 SpecficQuery = new String("Update request set evluationReport = "+ reportID +" where id ="+req.getRequestId());
					
				System.out.println(SpecficQuery);
				int ok = stmt.executeUpdate(SpecficQuery);
				
				
				
			//	updateStstusToRequest(req.getRequestId(), new Status(RequestStepStatusEnum.EVL_PASSED, RequestMainStatusEnum.OPEN) ,"Evaluation Report generated",0);
			
				System.out.println("Server - UpdateToDB : Add new evaluation report ? "+ok);
				if (ok == 1 ){
					AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), new Boolean(true));
				}
				else{
					AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), new Boolean(false));
				}
				stmt.close();
				return AnswerMessage;
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
				
		}
		System.out.println("Could not downcast properly in DBRequest::setEvaluationReport ");
		return  new Message(msg.getOpCode()+1000, msg.getMessageID(), new Boolean(false));     	
	}	
	
	
	public Message setTestingReport(Message msg) 
	{
		
		int reportID = -1;
		Request req = null;
		System.out.println("setTestingReport before reflexion");
	
		if (msg.getDataGram() instanceof Object[] && ((Object[])msg.getDataGram())[0] != null &&
			                                       	((Object[])msg.getDataGram())[1] != null &&
						                            ((Object[])msg.getDataGram())[0] instanceof Integer &&
				                                     ((Object[])msg.getDataGram())[1] instanceof Request &&
				                                     (Integer)((Object[])msg.getDataGram())[0] > 0)
		{
			 reportID = (Integer)((Object[])msg.getDataGram())[0];
			  req =  (Request) ((Object[])msg.getDataGram())[1] ;
			System.out.println("setTestingReport after reflexion");
			Message AnswerMessage;
			
			Statement stmt;
			try 
			{
				stmt = conn.createStatement();
				System.out.println("after create stsmt");
		
				String SpecficQuery=null;
				
				SpecficQuery = new String("Update request set testFailureReport = "+ reportID +" where id ="+req.getRequestId());
				
				
				System.out.println(SpecficQuery);
				int ok = stmt.executeUpdate(SpecficQuery);
			//	updateStstusToRequest(req.getRequestId(), new Status(RequestStepStatusEnum.EVL_PASSED, RequestMainStatusEnum.OPEN) ,"Evaluation Report generated",0);
			
				System.out.println("Server - UpdateToDB : Add new testin failure report ? "+ok);
				if (ok == 1 ){
					AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), new Boolean(true));
				}
				else{
					AnswerMessage = new Message(msg.getOpCode()+1000, msg.getMessageID(), new Boolean(false));
				}
				stmt.close();
				return AnswerMessage;
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
				
		}
		System.out.println("Could not downcast properly in DBRequest::setTestingReport ");
		return  new Message(msg.getOpCode()+1000, msg.getMessageID(), new Boolean(false));     	
	}	
}
